#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2022/11/24 11:48
# @Author : 闫峰
# @File    : sql_server_utils.py
# encoding=utf8
# import multiprocessing_run
from datetime import datetime
from typing import Union

import pymssql

from conf.settings import ensure_path_sep
from utils.readFileUtils.yaml_control import GetYamlData
import pyodbc

class MSSQL:

    def __init__(self, database_name):
        # database_name = "sqlserver:config.yaml:sqlserver_erpTrunkTest_db:irobotbox"
        yaml_path, database_key, db_name = database_name.split(":")
        yaml_path = ensure_path_sep(f"\\conf\\{yaml_path}")
        mysql = GetYamlData(yaml_path).get_yaml_data()[database_key]
        self.host = mysql['host']
        self.user = mysql['user']
        self.pwd = mysql['password']
        self.db = mysql['db_name']
        self.charset = mysql['charset']

    def __GetConnect(self):
        """
        得到连接信息
        返回: conn.cursor()
        """
        if not self.db:
            raise (NameError, "没有设置数据库信息")
        # self.conn = pymssql.connect(host=self.host, user=self.user, password=self.pwd, database=self.db, charset=self.charset, as_dict=True)
        # self.conn = pymssql.connect(host=self.host, user=self.user, password=self.pwd, database=self.db,
        #                             charset=self.charset)
        #zq切换数据源包为pyodbc
        self.conn = pyodbc.connect(f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={self.host},1433;DATABASE={self.db};UID={self.user};PWD={self.pwd}')

        cur = self.conn.cursor()
        if not cur:
            raise (NameError, "连接数据库失败")
        else:
            return cur

    def __GetConnect_dict(self):
        """
        得到连接信息
        返回: conn.cursor()
        """
        if not self.db:
            raise (NameError, "没有设置数据库信息")
        # self.conn = pymssql.connect(host=self.host, user=self.user, password=self.pwd, database=self.db,
        #                             charset=self.charset, as_dict=True)
        # zq切换数据源包为pyodbc
        self.conn = pyodbc.connect(f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={self.host},1433;DATABASE={self.db};UID={self.user};PWD={self.pwd}')
        # self.conn = pymssql.connect(host=self.host, user=self.user, password=self.pwd, database=self.db, charset=self.charset)
        cur = self.conn.cursor()
        if not cur:
            raise (NameError, "连接数据库失败")
        else:
            return cur

    def ExecQuery(self, sql, value):
        """
        执行查询语句
        返回的是一个包含tuple的list，list的元素是记录行，tuple的元素是每行记录的字段

        """
        cur = self.__GetConnect()
        cur.execute(sql, value)
        resList = cur.fetchall()

        # 查询完毕后必须关闭连接
        self.conn.close()
        return resList
    #zq测试
    def ExecQuery_dict(self, sql):
        """
        执行查询语句
        返回的是一个包含tuple的list，list的元素是记录行，tuple的元素是每行记录的字段

        """
        cur = self.__GetConnect_dict()
        # cur.execute(sql, value)
        # resList = cur.fetchall()
        #zq切换数据源包为pyodbc后修改查询转字典
        cur.execute(sql)
        columns = [column[0] for column in cur.description]
        resList = [dict(zip(columns, row)) for row in cur.fetchall()]
        # 查询完毕后必须关闭连接
        self.conn.close()
        return resList

    def ExecNonQuery(self, sql):
        """
        执行非查询语句

        调用示例：
            cur = self.__GetConnect()
            cur.execute(sql)
            self.conn.commit()
            self.conn.close()
        """
        cur = self.__GetConnect()
        cur.execute(sql)
        self.conn.commit()
        self.conn.close()

    def verify(self, result: dict) -> Union[dict, None]:
        """验证结果能否被json.dumps序列化"""
        # 尝试变成字符串，解决datetime 无法被json 序列化问题
        try:
            ceshi.dumps(result)
        except TypeError:  # TypeError: Object of type datetime is not JSON serializable
            for k, v in result.items():
                if isinstance(v, datetime):
                    result[k] = str(v)
        return result

    def close(self):
        self.close()


if __name__ == '__main__':
    sql1 = "SELECT a.Id AS BannedId,\
a.KeyName AS KeyName,\
a.UpdateTime AS UpdateTime,\
a.Remarks AS Remarks,\
a.IsStartedService AS IsAutoSet,\
a.ServiceExecutedSKUCount AS ServiceExecutedSKUCount,\
a.ClassId AS ClassId,\
e.Name AS ClassName,\
b.Word AS Word,\
c.PlatformId AS PlatformId,\
c.CountryCodes AS  CountryCodes,\
e.UpdateTime \
FROM E_BannedProduct a\
LEFT JOIN E_BannedProduct_KeyNames d ON a.ID=d.BannedId\
LEFT JOIN  E_BannedProduct_MultiLang b ON a.ID=b.BannedId\
LEFT JOIN E_BannedClass e ON a.ClassId=e.Id\
LEFT JOIN E_BannedClass_Platform c ON c.MainId=e.id\
WHERE  b.Word IS NOT NULL "

    # sql2 = "select top 10 OrderSourceSKUID from RB_Amazon_Product_Base (nolock) pb where pb.CustomerID=1 and pb.IsFBA=1 and pb.IsDelete=0;"
    db = MSSQL("erp_server")
    # 查询所有的审核不通过的sku
    # sql2 = 'SELECT Sku FROM p_market_product_review WHERE CustomerId=1 AND MarketId=81 AND SiteCode="AU" AND ReviewStatus=%s'
    res1 = db.ExecQuery(sql1)
    print(res1)
    #
    # a = 0
    # for i in res1:
    #     print(type(i))
    #     a += 1
    # print(a)
